﻿using Dapper;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using WMS.Core;

namespace WMS.Data.Base
{
    public class BaseDAL<T> : BaseRepository
    {
        public Guid EntityGuId { get; private set; } = Guid.Empty;

        public BaseDAL(string connectionString)
        {
            ConnectionString = connectionString;
            if (this.EntityGuId == Guid.Empty)
            {
                this.EntityGuId = typeof(T).GUID;
                GetEntityCache(this.EntityGuId);
            }
        }

        private CacheEntity GetEntityCache(Guid classId)
        {
            CacheEntity ret = null;
            DbStructCahce.Instance.Datas.TryGetValue(classId, out ret);
            if (ret == null)
            {
                ret = new CacheEntity(typeof(T));
                DbStructCahce.Instance.Datas.Add(ret.ClassId, ret);
            }
            return ret;
        }

        /// <summary>
        /// 根据sql, 查询列表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected async Task<List<T>> QueryAsync(string sql, object obj = null)
        {
            try
            {
                var db = base.DbContext;
                {
                    IEnumerable<T> result;
                    if (obj != null)
                    {
                        result = await db.QueryAsync<T>(sql, obj);
                    }
                    else
                    {
                        result = await db.QueryAsync<T>(sql);
                    }
                    return result.ToList();
                }
            }
            catch(Exception ex)
            {
                return new List<T>();
            }

        }

        /// <summary>
        /// 执行sql
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        protected async Task<int> ExecuteAsync(string sql, object obj = null)
        {
            var ret = 0;
            try
            {
                var db = base.DbContext;
                if (obj != null)
                {
                    ret = await db.ExecuteAsync(sql, obj);
                }
                else
                {
                    ret = await db.ExecuteAsync(sql);
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, obj, ex);
                ret = -1;
            }
            return ret;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        protected async Task<PagedList<T>> QueryPagedAsync(string sql, int pageIndex, int pageSize)
        {
            var ret = new PagedList<T>();
            var db = base.DbContext;
            var result = await db.QueryAsync<T>(sql);

            ret.PageIndex = pageIndex;
            ret.PageSize = pageSize;
            ret.RecordCount = result.LongCount();
            var skip = (pageIndex - 1) * pageSize;
            var take = pageSize;

            var list = result.Skip(skip).Take(take);
            ret.Datas.AddRange(list);

            return ret;
        }

        /// <summary>
        /// 根据ID, 获取对应记录的实体对象.
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<T> QueryByIdAsync(string Id)
        {
            var entStruct = GetEntityCache(this.EntityGuId);
            var tableName = entStruct.TableName;

            var sql = $"SELECT * from {tableName} where Id =@ID";
            var db = base.DbContext;
            {
                var result = await db.QueryFirstOrDefaultAsync<T>(sql, new { ID = Id });
                return result;
            }
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public async Task<List<T>> QueryListAsync()
        {
            var entStruct = GetEntityCache(this.EntityGuId);
            var tableName = entStruct.TableName;

            var sql = $"select * from {tableName}";
            try
            {
                var db = base.DbContext;
                {
                    var result = await db.QueryAsync<T>(sql);
                    return result.ToList();
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, ex);
                return new List<T>();
            }
        }

        /// <summary>
        /// In操作
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryInAsync(int[] ids)
        {
            var db = base.DbContext;
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var sql = $"select * from {tableName} where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                var result = await db.QueryAsync<T>(sql, new { ids });
                return result.ToList();
            }
        }

        /// <summary>
        /// In操作
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        public async Task<List<T>> QueryInIdsAsync(string[] ids)
        {
            var db = base.DbContext;
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var sql = $"select * from {tableName} where id in @ids";
                //参数类型是Array的时候，dappper会自动将其转化
                var result = await db.QueryAsync<T>(sql, new { ids });
                return result.ToList();
            }
        }

        /// <summary>
        /// 插入一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> InsertAsync(T entity)
        {
            var sql = string.Empty;
            try
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var columns = new List<string>();
                var paramsters = new List<string>();
                var values = new Dictionary<string, object> { };
                foreach (var filed in entStruct.Fileds)
                {
                    var prop = filed.Prop;
                    var propValue = prop.GetValue(entity);              //T类的实例obj, 上的属性值.

                    columns.Add(filed.FieldName);
                    paramsters.Add($"@{filed.FieldName}");
                    values.Add(filed.FieldName, propValue);
                }
                sql =
                    $"INSERT INTO {tableName}(" +
                        $"{ string.Join(",", columns)}" +
                    $") VALUES(" +
                        $"{ string.Join(",", paramsters)}" +
                    $")";

                var db = base.DbContext;
                {
                    var res = await db.ExecuteAsync(sql, values);
                    return res;
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, ex);
                return -1;
            }
        }

        /// <summary>
        /// 修改一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> UpdateAsync(T entity)
        {
            var sql = string.Empty;
            try
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var sqlSets = new List<string>();
                var sqlWhere = new List<string>();
                var values = new Dictionary<string, object> { };

                foreach (var filed in entStruct.Fileds)
                {
                    var prop = filed.Prop;
                    var propValue = prop.GetValue(entity);              //T类的实例obj, 上的属性值.

                    var line = $"{filed.FieldName} = @{filed.FieldName}";
                    if (filed.IsKey)
                    {
                        sqlWhere.Add(line);
                    }
                    else
                    {
                        sqlSets.Add(line);
                    }
                    values.Add(filed.FieldName, propValue);
                }

                var db = base.DbContext;
                {
                    sql = $"UPDATE {tableName} SET {string.Join(",", sqlSets)}";
                    if (sqlWhere.Count > 0)
                    {
                        sql += $" WHERE {string.Join(" AND ", sqlWhere)}";
                    }
                    var res = await db.ExecuteAsync(sql, values);
                    return res;
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, ex);
                return -1;
            }
        }

        /// <summary>
        ///  删除一条数据
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        public async Task<int> DeleteAsync(T entity)
        {
            var sql = string.Empty;
            try
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var sqlWhere = new List<string>();
                var values = new Dictionary<string, object> { };

                foreach (var filed in entStruct.Fileds)
                {
                    var prop = filed.Prop;
                    var propValue = prop.GetValue(entity);              //T类的实例obj, 上的属性值.

                    var line = $"{filed.FieldName} = @{filed.FieldName}";
                    if (filed.IsKey)
                    {
                        sqlWhere.Add(line);
                        values.Add(filed.FieldName, propValue);
                    }
                }

                var db = base.DbContext;
                {
                    if (sqlWhere.Count > 0)
                    {
                        sql = $"DELETE FROM {tableName} WHERE {string.Join(" AND ", sqlWhere)}";
                    }
                    var res = await db.ExecuteAsync(sql, values);
                    return res;
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, ex);
                return -1;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public async Task<int> DeleteByIdAsync(string id)
        {
            var sql = string.Empty;
            try
            {
                var entStruct = GetEntityCache(this.EntityGuId);
                var tableName = entStruct.TableName;

                var sqlWhere = new List<string>();
                var values = new Dictionary<string, object> { };

                var IdFiled = entStruct.Fileds
                    .Find(x => x.FieldName.Equals("Id", StringComparison.CurrentCultureIgnoreCase));
                if (IdFiled == null)
                {
                    throw new Exception("没有找到名为Id的字段.");
                }
                values.Add(IdFiled.FieldName, id);
                var db = base.DbContext;
                {
                    if (sqlWhere.Count > 0)
                    {
                        sql = $"DELETE FROM {tableName} WHERE {IdFiled.FieldName} = @{IdFiled.FieldName} ";
                    }
                    var res = await db.ExecuteAsync(sql, values);
                    return res;
                }
            }
            catch (Exception ex)
            {
                Logs.Instance.OutSqlEx(sql, ex);
                return -1;
            }
        }

    }

}
